package util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.nio.channels.DatagramChannel;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.aspectj.weaver.reflect.ReflectionBasedResolvedMemberImpl;
import org.junit.Test;

import poi.CellRegion;

/**
 * 导入Eecel 将数据生成到 List集合中 对表格有要求: 第一行是标题,后面是内容，内容是根据实体的属性去设置的。
 * 
 * @author Administrator
 *
 */
public class ImportExcel {

	private POIFSFileSystem fs;
	private HSSFWorkbook wb;
	private HSSFSheet sheet;
	private HSSFRow row;
	String[] title;

	/**
	 * 读取Excel 返回map 值 map中的title 是其标题 map中的datas是具体的属性值
	 * 
	 * @param fileName
	 *            excel文件的名称
	 * @return
	 */
	public Map<String, Object> imports(String fileName) {
		Map<String, Object> map = null;
		InputStream is = null;
		try {
			is = new FileInputStream(fileName);
			String[] title = readExcelTitle(is);
			List<Map<String, Object>> datas = readExcelContent(is);
			map = MapUtil.createMap("title", title, "datas", datas);
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (is != null)
					is.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return map;
	}

	/**
	 * 读取Excel 的标题部分
	 * 
	 * @param is
	 * @return
	 */
	public String[] readExcelTitle(InputStream is) {
		try {
			fs = new POIFSFileSystem(is);
			wb = new HSSFWorkbook(fs);
		} catch (IOException e) {
			e.printStackTrace();
		}
		sheet = wb.getSheetAt(0);
		row = sheet.getRow(0);
		// 标题总列数
		int colNum = row.getPhysicalNumberOfCells();
		title = new String[colNum];
		for (int i = 0; i < colNum; i++) {
			title[i] = (String) getCellValue(row.getCell((short) i));
		}
		return title;
	}

	/**
	 * 读取Excel数据内容
	 * 
	 * @param InputStream
	 * @return Map 包含单元格数据内容的Map对象
	 */
	public List<Map<String, Object>> readExcelContent(InputStream is) {
		List<Map<String, Object>> datas = new ArrayList<Map<String, Object>>();
		try {
			fs = new POIFSFileSystem(is);
			wb = new HSSFWorkbook(fs);
		} catch (IOException e) {
			e.printStackTrace();
		}
		sheet = wb.getSheetAt(0);
		// 得到总行数
		int rowNum = sheet.getLastRowNum();
		row = sheet.getRow(0);
		int colNum = row.getPhysicalNumberOfCells();
		// 正文内容应该从第二行开始,第一行为表头的标题
		for (int i = 1; i <= rowNum; i++) {

			row = sheet.getRow(i);
			if (row == null) {
				continue;
			}
			Map<String, Object> map = new HashMap<String, Object>();
			for (int j = 0; j < title.length; j++) {
				map.put(title[j], getCellValue(row.getCell(j)));
			}
			datas.add(map);
		}
		return datas;
	}

	// 判断指定的单元格是否是合并单元格
	private static boolean isMergedRegion(Sheet sheet, int row, int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress range = sheet.getMergedRegion(i);
			int firstColumn = range.getFirstColumn();
			int lastColumn = range.getLastColumn();
			int firstRow = range.getFirstRow();
			int lastRow = range.getLastRow();
			if (row >= firstRow && row <= lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					return true;
				}
			}
		}
		return false;
	}

	// 获取合并单元格的值
	public CellRegion getMergedRegionValue(Sheet sheet, int row, int column) {
		CellRegion r = new CellRegion();
		int sheetMergeCount = sheet.getNumMergedRegions();

		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress ca = sheet.getMergedRegion(i);
			int firstColumn = ca.getFirstColumn();
			int lastColumn = ca.getLastColumn();
			int firstRow = ca.getFirstRow();
			int lastRow = ca.getLastRow();

			if (row >= firstRow && row <= lastRow) {

				if (column >= firstColumn && column <= lastColumn) {
					Row fRow = sheet.getRow(firstRow);
					Cell fCell = fRow.getCell(firstColumn);
					r.startrownum = firstRow;
					r.endrownum = lastRow;
					r.value = getCellValue(fCell);
					return r;
				}
			}
		}

		return null;
	}

	/**
	 * 获取单元格的值 根据单元格的不同类型去 返回通的数据类型的值
	 * 
	 * @param cell
	 * @return
	 */
	public Object getCellValue(Cell cell) {

		if (cell == null)
			return "";

		if (cell.getCellTypeEnum() == CellType.STRING) {

			return cell.getStringCellValue();

		} else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {

			return cell.getBooleanCellValue();

		} else if (cell.getCellTypeEnum() == CellType.NUMERIC) {

			return cell.getNumericCellValue();

		} else if (cell.getCellTypeEnum() == CellType.FORMULA) {

			return cell.getNumericCellValue();

		} else if (HSSFDateUtil.isCellDateFormatted(cell)) {

			if ("yyyy/mm;@".equals(cell.getCellStyle().getDataFormatString())
					|| "m/d/yy".equals(cell.getCellStyle().getDataFormatString())
					|| "yy/m/d".equals(cell.getCellStyle().getDataFormatString())
					|| "mm/dd/yy".equals(cell.getCellStyle().getDataFormatString())
					|| "dd-mmm-yy".equals(cell.getCellStyle().getDataFormatString())
					|| "yyyy/m/d".equals(cell.getCellStyle().getDataFormatString())) {

				return new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue());

			} else {
				return cell.getStringCellValue();
			}

		}
		return "";
	}

	// 求单元格或者合并单元格的高度
	public int getheight(Sheet sheet, int rownum, int colnum) {
		if (isMergedRegion(sheet, rownum, colnum)) {
			CellRegion r = getMergedRegionValue(sheet, rownum, colnum);
			return r.endrownum - r.startrownum + 1;
		} else
			return 1;
	}

	// 获取合并或未合并的任意单元格
	public String getvalue(Sheet sheet, int rownum, int colnum) {
		if (isMergedRegion(sheet, rownum, colnum)) {
			CellRegion c = getMergedRegionValue(sheet, rownum, colnum);
			return (String) c.value;
		} else {
			Row row = sheet.getRow(rownum);
			Cell cell = row.getCell(colnum);
			return (String) getCellValue(cell);
		}
	}

	@SuppressWarnings("unchecked")
	@Test
	public void test() {
		ImportExcel util = new ImportExcel();

		Map<String, Object> map = util.imports("D:/1.xls");

		String[] title = (String[]) map.get("title");
		for (String t : title) {
			System.out.println(t);
		}
        
		List<Map<String, Object>> datas = (List<Map<String, Object>>) map.get("datas");

		System.out.println("-------data------------");
		for (Map<String, Object> m : datas) {
			Set<Entry<String, Object>> entrySet = m.entrySet();
			for (Entry<String, Object> entry : entrySet) {
				System.out.print(entry.getKey() + "---" + entry.getValue() + ";");
			}
			System.out.println("--");
		}
	}

}
